from flask import jsonify
from sqlalchemy import text

from tzq.database_compare_system import dcs_bp
from tzq.extensions import db


@dcs_bp.route('/api/initCompareTableJobResult/<int:id>', methods=['GET'])
def init_compare_table_job_result(id):
    """
        初始化比对模型任务结果数据信息
        1、比对任务，把比对表模型的结果插入到 dc_comp_tab_job_result_t 表
    """
    # compareTask = CompareTask.query.get_or_404(id)
    # print("id=" + str(id))
    try:
        v_sql = text("""
delete from dc_comp_tab_job_result_t where comp_task_id = :id ;
insert into dc_comp_tab_job_result_t (comp_task_id,comp_task_name,schema,table_name,column_name,total_check_result,table_name_check_result,table_name1,table_name2,table_name3,table_name4,table_name5,table_name6,table_name7,column_name_check_result,column_name1,column_name2,column_name3,column_name4,column_name5,column_name6,column_name7,data_type_check_result,data_type1,data_type2,data_type3,data_type4,data_type5,data_type6,data_type7,data_default_check_result,data_default1,data_default2,data_default3,data_default4,data_default5,data_default6,data_default7,pkey_check_result,pkey1,pkey2,pkey3,pkey4,pkey5,pkey6,pkey7,nullable_check_result,nullable1,nullable2,nullable3,nullable4,nullable5,nullable6,nullable7,table_comment_check_result,table_comment1,table_comment2,table_comment3,table_comment4,table_comment5,table_comment6,table_comment7,column_comment_check_result,column_comment1,column_comment2,column_comment3,column_comment4,column_comment5,column_comment6,column_comment7)
with c as (
  select comp_task_id,comp_task_name
    from dc_comp_task_t
   where comp_task_id = :id
)
, constant as (
  -- 数据源常量配置
  select s1.connect_name as source_name1
        ,s2.connect_name as source_name2
        ,s3.connect_name as source_name3
        ,s4.connect_name as source_name4
        ,s5.connect_name as source_name5
        ,s6.connect_name as source_name6
        ,s7.connect_name as source_name7
    from dc_comp_task_t t 
    left join dc_database_info_t s1 on t.db_info_1_id = s1.db_info_id
    left join dc_database_info_t s2 on t.db_info_2_id = s2.db_info_id
    left join dc_database_info_t s3 on t.db_info_3_id = s3.db_info_id
    left join dc_database_info_t s4 on t.db_info_4_id = s4.db_info_id
    left join dc_database_info_t s5 on t.db_info_5_id = s5.db_info_id
    left join dc_database_info_t s6 on t.db_info_6_id = s6.db_info_id
    left join dc_database_info_t s7 on t.db_info_7_id = s7.db_info_id
   where 1=1
     and t.comp_task_id = (select comp_task_id from c))
, ss1 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name1 from constant))
, ss2 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name2 from constant))
, ss3 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name3 from constant))
, ss4 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name4 from constant))
, ss5 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name5 from constant))
, ss6 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name6 from constant))
, ss7 as (select distinct schema, table_name from dc_table_info_t where source = (select source_name7 from constant))
,tab_base_info_tmp as (
    select schema, table_name from ss1 UNION
    select schema, table_name from ss2 UNION
    select schema, table_name from ss3 UNION
    select schema, table_name from ss4 UNION
    select schema, table_name from ss5 UNION
    select schema, table_name from ss6 UNION
    select schema, table_name from ss7)
,tab_base_info as (
select distinct t.schema,t.table_name 
       -- 表是否存在
      ,case when COALESCE(ss1.table_name,'tzqnull') = COALESCE(ss2.table_name,'tzqnull') and
			        (COALESCE(ss1.table_name,'tzqnull') = COALESCE(ss3.table_name,'tzqnull') or ((select count(*) from ss3) = 0)) and
			        (COALESCE(ss1.table_name,'tzqnull') = COALESCE(ss4.table_name,'tzqnull') or ((select count(*) from ss4) = 0)) and
			        (COALESCE(ss1.table_name,'tzqnull') = COALESCE(ss5.table_name,'tzqnull') or ((select count(*) from ss5) = 0)) and
			        (COALESCE(ss1.table_name,'tzqnull') = COALESCE(ss6.table_name,'tzqnull') or ((select count(*) from ss6) = 0)) and
			        (COALESCE(ss1.table_name,'tzqnull') = COALESCE(ss7.table_name,'tzqnull') or ((select count(*) from ss7) = 0))
            then '√' else '×' end as table_name_check_result
      ,case COALESCE(ss1.table_name,'tzqnull') when 'tzqnull' then '×' else '√' end as table_name1
      ,case COALESCE(ss2.table_name,'tzqnull') when 'tzqnull' then '×' else '√' end as table_name2
      ,case when COALESCE(ss3.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss3) != 0 then '×' 
            when COALESCE(ss3.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss3) = 0 then null else '√' end as table_name3
      ,case when COALESCE(ss4.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss4) != 0 then '×' 
            when COALESCE(ss4.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss4) = 0 then null else '√' end as table_name4
      ,case when COALESCE(ss5.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss5) != 0 then '×' 
            when COALESCE(ss5.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss5) = 0 then null else '√' end as table_name5
      ,case when COALESCE(ss6.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss6) != 0 then '×' 
            when COALESCE(ss6.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss6) = 0 then null else '√' end as table_name6
      ,case when COALESCE(ss7.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss7) != 0 then '×' 
            when COALESCE(ss7.table_name,'tzqnull') = 'tzqnull' and (select count(*) from ss7) = 0 then null else '√' end as table_name7
  from tab_base_info_tmp t
  left join ss1 on ss1.schema = t.schema and ss1.table_name = t.table_name
  left join ss2 on ss2.schema = t.schema and ss2.table_name = t.table_name
  left join ss3 on ss3.schema = t.schema and ss3.table_name = t.table_name
  left join ss4 on ss4.schema = t.schema and ss4.table_name = t.table_name
  left join ss5 on ss5.schema = t.schema and ss5.table_name = t.table_name
  left join ss6 on ss6.schema = t.schema and ss6.table_name = t.table_name
  left join ss7 on ss7.schema = t.schema and ss7.table_name = t.table_name
 order by t.schema, t.table_name)
, s1 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name1 from constant))
, s2 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name2 from constant))
, s3 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name3 from constant))
, s4 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name4 from constant))
, s5 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name5 from constant))
, s6 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name6 from constant))
, s7 as (select schema, table_name, table_comment, column_name, data_type, data_default, nullable, pkey, column_comment
  from dc_table_info_t where source = (select source_name7 from constant))
,base_info as (
    select distinct schema, table_name, column_name from s1 UNION
    select distinct schema, table_name, column_name from s2 UNION
    select distinct schema, table_name, column_name from s3 UNION
    select distinct schema, table_name, column_name from s4 UNION
    select distinct schema, table_name, column_name from s5 UNION
    select distinct schema, table_name, column_name from s6 UNION
    select distinct schema, table_name, column_name from s7)
,tmp1 as (
select t.schema, t.table_name, t.column_name
      ,t1.table_name_check_result
      ,t1.table_name1
      ,t1.table_name2
      ,t1.table_name3
      ,t1.table_name4
      ,t1.table_name5
      ,t1.table_name6
      ,t1.table_name7
       -- 字段是否存在
      ,case when COALESCE(s1.column_name,'tzqnull') = COALESCE(s2.column_name,'tzqnull') and
			        (COALESCE(s1.column_name,'tzqnull') = COALESCE(s3.column_name,'tzqnull') or ((select count(*) from s3) = 0)) and
			        (COALESCE(s1.column_name,'tzqnull') = COALESCE(s4.column_name,'tzqnull') or ((select count(*) from s4) = 0)) and
			        (COALESCE(s1.column_name,'tzqnull') = COALESCE(s5.column_name,'tzqnull') or ((select count(*) from s5) = 0)) and
			        (COALESCE(s1.column_name,'tzqnull') = COALESCE(s6.column_name,'tzqnull') or ((select count(*) from s6) = 0)) and
			        (COALESCE(s1.column_name,'tzqnull') = COALESCE(s7.column_name,'tzqnull') or ((select count(*) from s7) = 0))
            then '√' else '×' end as column_name_check_result
      ,case COALESCE(s1.column_name,'tzqnull') when 'tzqnull' then '×' else '√' end as column_name1
      ,case COALESCE(s2.column_name,'tzqnull') when 'tzqnull' then '×' else '√' end as column_name2
      ,case when COALESCE(s3.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s3) != 0 then '×' 
            when COALESCE(s3.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s3) = 0 then null else '√' end as column_name3
      ,case when COALESCE(s4.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s4) != 0 then '×' 
            when COALESCE(s4.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s4) = 0 then null else '√' end as column_name4
      ,case when COALESCE(s5.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s5) != 0 then '×' 
            when COALESCE(s5.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s5) = 0 then null else '√' end as column_name5
      ,case when COALESCE(s6.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s6) != 0 then '×' 
            when COALESCE(s6.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s6) = 0 then null else '√' end as column_name6
      ,case when COALESCE(s7.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s7) != 0 then '×' 
            when COALESCE(s7.column_name,'tzqnull') = 'tzqnull' and (select count(*) from s7) = 0 then null else '√' end as column_name7
       -- 字段类型 是否一致
      ,case when COALESCE( s1.data_type,'tzqnull') = COALESCE(s2.data_type,'tzqnull') and
              (COALESCE(s1.data_type,'tzqnull') = COALESCE(s3.data_type,'tzqnull') or ((select count(*) from s3) = 0)) and
              (COALESCE(s1.data_type,'tzqnull') = COALESCE(s4.data_type,'tzqnull') or ((select count(*) from s4) = 0)) and
              (COALESCE(s1.data_type,'tzqnull') = COALESCE(s5.data_type,'tzqnull') or ((select count(*) from s5) = 0)) and
              (COALESCE(s1.data_type,'tzqnull') = COALESCE(s6.data_type,'tzqnull') or ((select count(*) from s6) = 0)) and
              (COALESCE(s1.data_type,'tzqnull') = COALESCE(s7.data_type,'tzqnull') or ((select count(*) from s7) = 0))
           then '√'
         else '×'
       end as data_type_check_result
      ,case COALESCE(s1.data_type,'tzqnull') when 'tzqnull' then null else s1.data_type end as data_type1
      ,case COALESCE(s2.data_type,'tzqnull') when 'tzqnull' then null else s2.data_type end as data_type2
      ,case COALESCE(s3.data_type,'tzqnull') when 'tzqnull' then null else s3.data_type end as data_type3
      ,case COALESCE(s4.data_type,'tzqnull') when 'tzqnull' then null else s4.data_type end as data_type4
      ,case COALESCE(s5.data_type,'tzqnull') when 'tzqnull' then null else s5.data_type end as data_type5
      ,case COALESCE(s6.data_type,'tzqnull') when 'tzqnull' then null else s6.data_type end as data_type6
      ,case COALESCE(s7.data_type,'tzqnull') when 'tzqnull' then null else s7.data_type end as data_type7
      
      ,case when COALESCE( s1.data_default,'tzqnull') = COALESCE(s2.data_default,'tzqnull') and
         (COALESCE(s1.data_default,'tzqnull') = COALESCE(s3.data_default,'tzqnull') or ((select count(*) from s3) = 0)) and
         (COALESCE(s1.data_default,'tzqnull') = COALESCE(s4.data_default,'tzqnull') or ((select count(*) from s4) = 0)) and
         (COALESCE(s1.data_default,'tzqnull') = COALESCE(s5.data_default,'tzqnull') or ((select count(*) from s5) = 0)) and
         (COALESCE(s1.data_default,'tzqnull') = COALESCE(s6.data_default,'tzqnull') or ((select count(*) from s6) = 0)) and
         (COALESCE(s1.data_default,'tzqnull') = COALESCE(s7.data_default,'tzqnull') or ((select count(*) from s7) = 0))
       then '√' else '×' end as data_default_check_result
      ,case COALESCE(s1.data_default,'tzqnull') when 'tzqnull' then null else s1.data_default end as data_default1
      ,case COALESCE(s2.data_default,'tzqnull') when 'tzqnull' then null else s2.data_default end as data_default2
      ,case COALESCE(s3.data_default,'tzqnull') when 'tzqnull' then null else s3.data_default end as data_default3
      ,case COALESCE(s4.data_default,'tzqnull') when 'tzqnull' then null else s4.data_default end as data_default4
      ,case COALESCE(s5.data_default,'tzqnull') when 'tzqnull' then null else s5.data_default end as data_default5
      ,case COALESCE(s6.data_default,'tzqnull') when 'tzqnull' then null else s6.data_default end as data_default6
      ,case COALESCE(s7.data_default,'tzqnull') when 'tzqnull' then null else s7.data_default end as data_default7
      
      ,case when COALESCE( s1.pkey,'tzqnull') = COALESCE(s2.pkey,'tzqnull') and
              (COALESCE(s1.pkey,'tzqnull') = COALESCE(s3.pkey,'tzqnull') or ((select count(*) from s3) = 0)) and
              (COALESCE(s1.pkey,'tzqnull') = COALESCE(s4.pkey,'tzqnull') or ((select count(*) from s4) = 0)) and
              (COALESCE(s1.pkey,'tzqnull') = COALESCE(s5.pkey,'tzqnull') or ((select count(*) from s5) = 0)) and
              (COALESCE(s1.pkey,'tzqnull') = COALESCE(s6.pkey,'tzqnull') or ((select count(*) from s6) = 0)) and
              (COALESCE(s1.pkey,'tzqnull') = COALESCE(s7.pkey,'tzqnull') or ((select count(*) from s7) = 0))
            then '√' else '×' end as pkey_check_result
      ,case COALESCE(s1.pkey,'tzqnull') when 'tzqnull' then null else s1.pkey end as pkey1
      ,case COALESCE(s2.pkey,'tzqnull') when 'tzqnull' then null else s2.pkey end as pkey2
      ,case COALESCE(s3.pkey,'tzqnull') when 'tzqnull' then null else s3.pkey end as pkey3
      ,case COALESCE(s4.pkey,'tzqnull') when 'tzqnull' then null else s4.pkey end as pkey4
      ,case COALESCE(s5.pkey,'tzqnull') when 'tzqnull' then null else s5.pkey end as pkey5
      ,case COALESCE(s6.pkey,'tzqnull') when 'tzqnull' then null else s6.pkey end as pkey6
      ,case COALESCE(s7.pkey,'tzqnull') when 'tzqnull' then null else s7.pkey end as pkey7
      
      ,case when COALESCE( s1.nullable,'tzqnull') = COALESCE(s2.nullable,'tzqnull') and
              (COALESCE(s1.nullable,'tzqnull') = COALESCE(s3.nullable,'tzqnull') or ((select count(*) from s3) = 0)) and
              (COALESCE(s1.nullable,'tzqnull') = COALESCE(s4.nullable,'tzqnull') or ((select count(*) from s4) = 0)) and
              (COALESCE(s1.nullable,'tzqnull') = COALESCE(s5.nullable,'tzqnull') or ((select count(*) from s5) = 0)) and
              (COALESCE(s1.nullable,'tzqnull') = COALESCE(s6.nullable,'tzqnull') or ((select count(*) from s6) = 0)) and
              (COALESCE(s1.nullable,'tzqnull') = COALESCE(s7.nullable,'tzqnull') or ((select count(*) from s7) = 0))
            then '√' else '×' end as nullable_check_result
      ,case COALESCE(s1.nullable,'tzqnull') when 'tzqnull' then null else s1.nullable end as nullable1
      ,case COALESCE(s2.nullable,'tzqnull') when 'tzqnull' then null else s2.nullable end as nullable2
      ,case COALESCE(s3.nullable,'tzqnull') when 'tzqnull' then null else s3.nullable end as nullable3
      ,case COALESCE(s4.nullable,'tzqnull') when 'tzqnull' then null else s4.nullable end as nullable4
      ,case COALESCE(s5.nullable,'tzqnull') when 'tzqnull' then null else s5.nullable end as nullable5
      ,case COALESCE(s6.nullable,'tzqnull') when 'tzqnull' then null else s6.nullable end as nullable6
      ,case COALESCE(s7.nullable,'tzqnull') when 'tzqnull' then null else s7.nullable end as nullable7
      
      ,case when COALESCE( s1.table_comment,'tzqnull') = COALESCE(s2.table_comment,'tzqnull') and
         (COALESCE(s1.table_comment,'tzqnull') = COALESCE(s3.table_comment,'tzqnull') or ((select count(*) from s3) = 0)) and
         (COALESCE(s1.table_comment,'tzqnull') = COALESCE(s4.table_comment,'tzqnull') or ((select count(*) from s4) = 0)) and
         (COALESCE(s1.table_comment,'tzqnull') = COALESCE(s5.table_comment,'tzqnull') or ((select count(*) from s5) = 0)) and
         (COALESCE(s1.table_comment,'tzqnull') = COALESCE(s6.table_comment,'tzqnull') or ((select count(*) from s6) = 0)) and
         (COALESCE(s1.table_comment,'tzqnull') = COALESCE(s7.table_comment,'tzqnull') or ((select count(*) from s7) = 0))
       then '√' else '×' end as table_comment_check_result
      ,case COALESCE(s1.table_comment,'tzqnull') when 'tzqnull' then null else s1.table_comment end as table_comment1
      ,case COALESCE(s2.table_comment,'tzqnull') when 'tzqnull' then null else s2.table_comment end as table_comment2
      ,case COALESCE(s3.table_comment,'tzqnull') when 'tzqnull' then null else s3.table_comment end as table_comment3
      ,case COALESCE(s4.table_comment,'tzqnull') when 'tzqnull' then null else s4.table_comment end as table_comment4
      ,case COALESCE(s5.table_comment,'tzqnull') when 'tzqnull' then null else s5.table_comment end as table_comment5
      ,case COALESCE(s6.table_comment,'tzqnull') when 'tzqnull' then null else s6.table_comment end as table_comment6
      ,case COALESCE(s7.table_comment,'tzqnull') when 'tzqnull' then null else s7.table_comment end as table_comment7
      
      ,case when COALESCE( s1.column_comment,'tzqnull') = COALESCE(s2.column_comment,'tzqnull') and
         (COALESCE(s1.column_comment,'tzqnull') = COALESCE(s3.column_comment,'tzqnull') or ((select count(*) from s3) = 0)) and
         (COALESCE(s1.column_comment,'tzqnull') = COALESCE(s4.column_comment,'tzqnull') or ((select count(*) from s4) = 0)) and
         (COALESCE(s1.column_comment,'tzqnull') = COALESCE(s5.column_comment,'tzqnull') or ((select count(*) from s5) = 0)) and
         (COALESCE(s1.column_comment,'tzqnull') = COALESCE(s6.column_comment,'tzqnull') or ((select count(*) from s6) = 0)) and
         (COALESCE(s1.column_comment,'tzqnull') = COALESCE(s7.column_comment,'tzqnull') or ((select count(*) from s7) = 0))
       then '√' else '×' end as column_comment_check_result
      ,case COALESCE(s1.column_comment,'tzqnull') when 'tzqnull' then null else s1.column_comment end as column_comment1
      ,case COALESCE(s2.column_comment,'tzqnull') when 'tzqnull' then null else s2.column_comment end as column_comment2
      ,case COALESCE(s3.column_comment,'tzqnull') when 'tzqnull' then null else s3.column_comment end as column_comment3
      ,case COALESCE(s4.column_comment,'tzqnull') when 'tzqnull' then null else s4.column_comment end as column_comment4
      ,case COALESCE(s5.column_comment,'tzqnull') when 'tzqnull' then null else s5.column_comment end as column_comment5
      ,case COALESCE(s6.column_comment,'tzqnull') when 'tzqnull' then null else s6.column_comment end as column_comment6
      ,case COALESCE(s7.column_comment,'tzqnull') when 'tzqnull' then null else s7.column_comment end as column_comment7
  from base_info t
  left join tab_base_info t1 on t1.schema = t.schema and t1.table_name = t.table_name
  left join s1 on s1.schema = t.schema and s1.table_name = t.table_name and s1.column_name = t.column_name
  left join s2 on s2.schema = t.schema and s2.table_name = t.table_name and s2.column_name = t.column_name
  left join s3 on s3.schema = t.schema and s3.table_name = t.table_name and s3.column_name = t.column_name
  left join s4 on s4.schema = t.schema and s4.table_name = t.table_name and s4.column_name = t.column_name
  left join s5 on s5.schema = t.schema and s5.table_name = t.table_name and s5.column_name = t.column_name
  left join s6 on s6.schema = t.schema and s6.table_name = t.table_name and s6.column_name = t.column_name
  left join s7 on s7.schema = t.schema and s7.table_name = t.table_name and s7.column_name = t.column_name
 order by t.schema, t.table_name, t.column_name)
select c.comp_task_id, c.comp_task_name
      ,schema, table_name, column_name
      ,case
         when table_name_check_result = '√' and column_name_check_result = '√' and data_type_check_result = '√' AND 
              data_default_check_result = '√' and pkey_check_result = '√' AND nullable_check_result = '√' and 
              table_comment_check_result = '√' AND column_comment_check_result = '√'
           then '√'
         else '×'
       end::varchar as total_check_result
      , table_name_check_result, table_name1, table_name2, table_name3, table_name4, table_name5, table_name6, table_name7
      , column_name_check_result, column_name1, column_name2, column_name3, column_name4, column_name5, column_name6, column_name7
      , data_type_check_result, data_type1, data_type2, data_type3, data_type4, data_type5, data_type6, data_type7
      , data_default_check_result, data_default1, data_default2, data_default3, data_default4, data_default5, data_default6, data_default7
      , pkey_check_result, pkey1, pkey2, pkey3, pkey4, pkey5, pkey6, pkey7
      , nullable_check_result, nullable1, nullable2, nullable3, nullable4, nullable5, nullable6, nullable7
      , table_comment_check_result, table_comment1, table_comment2, table_comment3, table_comment4, table_comment5, table_comment6, table_comment7
      , column_comment_check_result, column_comment1, column_comment2, column_comment3, column_comment4, column_comment5, column_comment6, column_comment7
  from tmp1, c;
        """)
        db.session.execute(v_sql, {
            'id': id
        })
        db.session.commit()
        return jsonify({"code": 1, "msg": "【OK】"})
    except Exception as e:
        db.session.rollback()
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500
